package com.xiaoma.tpo.dao;

import java.util.List;
import java.util.Map;

import javax.xml.stream.events.StartDocument;

import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import com.xiaoma.tpo.baseDao.BaseDaoImpl;
import com.xiaoma.tpo.domain.AdAd;
import com.xiaoma.tpo.domain.VVersion;
import com.xiaoma.tpo.dto.AdCountVO;
import com.xiaoma.tpo.dto.AdVo;
import com.xiaoma.tpo.utils.Pager;
import com.xiaoma.verbal.baseDao.DefaultRowMapper;
@Repository
public class AdAdDao extends BaseDaoImpl<AdAd>{

	public Pager listAd(String day,String sStatus,String sPosition,String sName,Integer page, Integer rows) {
		Pager pager = new Pager();
		Integer startNum;
		
		if(page == null || page == 0){
			startNum = 0;
			
		}else{
			startNum = (page-1)*rows;
			pager.setCurPage(page);
		}
		if(rows == null ){
			rows = pager.getItemsPerPage();
		}else{
			pager.setItemsPerPage(rows);
		}
		if(day != null && day != ""){
			
			String sqlCount = " select count(aa.id) "
					+ " from ad_ad aa , ad_position ap "
					+ " where aa.position_id = ap.id "
					+ " and update_date > '"+day+" 00:00:00' and update_date < '"+day+" 23:59:59' ";
			String sqlList = "select aa.id , aa.position_id , aa.description , aa.content , aa.status , aa.image_url,"
					+ " aa.create_date , aa.update_date,  ap.position , ap.name "
					+ " from ad_ad aa , ad_position ap "
					+ " where aa.position_id = ap.id "
					+ " and  update_date > '"+day+" 00:00:00' and update_date < '"+day+" 23:59:59' desc";
			if(sStatus != null && sStatus!=""){
				sqlCount = sqlCount + " and aa.status = "+sStatus;
				sqlList = sqlList + " and aa.status = "+sStatus;
			}
			if(sPosition != null && sPosition != ""){
				sqlCount = sqlCount + " and aa.position_id = "+sPosition;
				sqlList = sqlList + " and aa.position_id = "+sPosition;
			}
//			sqlCount = sqlCount  + " limit "+startNum+","+rows ;
			sqlList  = sqlList  + " order by aa.update_date limit "+startNum+","+rows ;
			Integer totalCount = jdbcTemplate.queryForInt(sqlCount);
			List<AdVo> adVos = jdbcTemplate.query(sqlList, new DefaultRowMapper(AdVo.class, this.getNameHandler()));
			pager.setList(adVos);
			pager.setTotalCount(totalCount);
			
			
		}else{
			String sqlCount = " select count(aa.id) "
					+ " from ad_ad aa , ad_position ap"
					+ " where aa.position_id = ap.id ";
			Integer totalCount = jdbcTemplate.queryForInt(sqlCount);
			String sqlList = " select aa.id , aa.position_id , aa.description , aa.content , aa.status , aa.image_url,"
					+ " aa.create_date , aa.update_date,  ap.position , ap.name"
					+ " from ad_ad aa , ad_position ap"
					+ " where aa.position_id = ap.id ";
			if(sStatus != null && sStatus != ""){
				sqlCount = sqlCount + " and aa.status = "+sStatus;
				sqlList = sqlList + " and aa.status = "+sStatus;
			}
			if(sPosition != null && sPosition != ""){
				sqlCount = sqlCount + " and aa.position_id = "+sPosition;
				sqlList = sqlList + " and aa.position_id = "+sPosition;
			}
			if(sName != null && sName != ""){
				sqlCount = sqlCount + " and ap.name = '"+sName+"'";
				sqlList = sqlList + " and ap.name = '"+sName+"'";
			}
//			sqlCount = sqlCount  + " limit "+startNum+","+rows ;
			sqlList  = sqlList  + " order by aa.update_date desc limit "+startNum+","+rows ;
			List<AdVo> adVos = jdbcTemplate.query(sqlList, new DefaultRowMapper(AdVo.class, this.getNameHandler()));
			pager.setList(adVos);
			pager.setTotalCount(totalCount);
			
		}
		return pager;
	}
	//对外提供的接口 获取所有广告 并以position分组
	public List<AdVo> getAllAd() {
		Pager pager = new Pager();
		Integer startNum;
		
//		String sqlCount = " select count(aa.id) num1"
//				+ " from ad_ad aa , ad_position ap"
//				+ " where aa.position_id = ap.id group by ap.position";
//		List<?> totalCount = jdbcTemplate.query(sqlCount);
		String sqlList = " select aa.id , aa.position_id , aa.description , aa.content , aa.status , aa.image_url,"
				+ " aa.create_date , aa.update_date,  ap.position , ap.name"
				+ " from ad_ad aa , ad_position ap"
				+ " where aa.position_id = ap.id  and aa.status='1'";
		sqlList  = sqlList  + " group by ap.position order by aa.update_date " ;
		List<AdVo> adVos = jdbcTemplate.query(sqlList, new DefaultRowMapper(AdVo.class, this.getNameHandler()));
		pager.setList(adVos);
//		pager.setTotalCount(totalCount);
		return adVos;
	}
	public boolean judgeActiveAd(AdVo adVo) {
		String sqlList = " select id , position_id , description , content , status , image_url,"
				+ " create_date , update_date"
				+ " from ad_ad  "
				+ " where position_id ="+adVo.getPositionId()+"  and  status='1'";
		List<VVersion> vVersion = jdbcTemplate.query(sqlList, new DefaultRowMapper(VVersion.class, this.getNameHandler()));
		if(vVersion != null && vVersion.size() > 0){
			if(adVo.getId() != null){
				if(vVersion.get(0).getId() == adVo.getId()){
					return false;
				}
			}
			return true;
		}
		return false;
	}
//	点击率
	public Pager adCount(Integer adId,Integer positionId,String platformGuid,Integer page,Integer rows) {
		Pager pager = new Pager();
		Integer startNum;
		if(page == null || page == 0){
			startNum = 0;
			
		}else{
			startNum = (page-1)*rows;
			pager.setCurPage(page);
		}
		if(rows == null ){
			rows = pager.getItemsPerPage();
		}else{
			pager.setItemsPerPage(rows);
		}
		String sqlCount = "select count(av.id)"
				+ " from ad_visit av, ad_ad aa, ad_position ap,  v_physical_address vpa"
				+ " where av.ad_id = aa.id "
				+ " and   av.position_id = ap.id"
				+ " and   av.platform_guid = vpa.guid";
		String sqlList = "select av.count ,av.ad_id,av.position_id,av.platform_guid,aa.description as ad_name,ap.name as position_name ,vpa.name as plateform_name"
				+ " from ad_visit av, ad_ad aa, ad_position ap,  v_physical_address vpa"
				+ " where av.ad_id = aa.id "
				+ " and   av.position_id = ap.id"
				+ " and   av.platform_guid = vpa.guid";
		if(adId != null && adId > 0){
			sqlCount = sqlCount +" and av.ad_id = "+adId;
			sqlList = sqlList+" and av.ad_id = "+adId;
		}
		if(positionId != null && positionId > 0){
			sqlCount = sqlCount +" and av.position_id = "+positionId;
			sqlList = sqlList+" and av.position_id = "+positionId;
		}
		if(platformGuid != null && platformGuid != ""){
			sqlCount = sqlCount +" and av.platform_guid = '"+platformGuid+"'";
			sqlList = sqlList+" and av.platform_guid = '"+platformGuid+"'";
		}
		sqlList = sqlList + " order by av.position_id  limit "+startNum+","+rows;
		Integer totalCount = jdbcTemplate.queryForInt(sqlCount);
		List<AdCountVO> lAdCountVO = jdbcTemplate.query(sqlList, new DefaultRowMapper(AdCountVO.class, this.getNameHandler()));
		pager.setTotalCount(totalCount);
		pager.setList(lAdCountVO);
		return pager;
	}

}
